clear all
set more off, perm
local mydir "`1'"
global Tables `mydir'/XS/Tables
global WorkXS `mydir'/XS/DataWork
global WorkTS `mydir'/TS/DataWork
global RawTS  `mydir'/TS/DataRaw
global RawXS  `mydir'/XS/DataRaw
global LastXS `mydir'/XS/DataForTables
/* ********************************************************************* */
* one_year.xlsx is from WRDS
import excel $RawTS/Yields/one_year.xlsx	, sheet("formatted") firstrow clear
rename TMYTM yield1

tempfile yield
save "`yield'", replace
**************
* ten_year.xlsx is from WRDS
import excel $RawTS/Yields/ten_year.xlsx	, sheet("formatted") firstrow clear
rename TMYTM yield10

merge 1:1 date using "`yield'"
drop _merge 

* date is a string
split date,g(part) p("/")
foreach var of varlist part* {
	destring `var', replace
}
* now put it back together
drop date
generate date=mdy(part1,part2,part3)

* note: FED uses first-of-the-month date convention but WRDS uses last-trading-day-of-the-month
generate observation_date=mdy(part1+1,1,part3  ) if part1<=11
replace  observation_date=mdy(1      ,1,part3+1) if part1==12

format date observation_date %td
drop part1-part3
save "`yield'", replace
**************
* AAA.xls is from St Louis Fed
import excel $RawTS/Yields/AAA.xls	, sheet("formatted") firstrow clear
merge 1:1 observation_date using "`yield'"
drop _merge
save "`yield'", replace
**************
* BAA.xls is from St Louis Fed
import excel $RawTS/Yields/BAA.xls	, sheet("formatted") firstrow clear
merge 1:1 observation_date using "`yield'"
drop _merge

replace date=mdy(month(observation_date),day(observation_date), year(observation_date))-1 if date==.

generate datem=mofd(date)
format datem %tm
save "`yield'", replace
**************
* Bloom data --- https://www.policyuncertainty.com/us_monthly.html
import excel $RawTS/US_Policy_Uncertainty_Data.xlsx, sheet("Main Index") cellrange(A1:D445) firstrow clear
destring Year, replace
gen datem=mofd(mdy(Month, 1,Year))
format datem %tm
rename Three_Component_Index   bloom_uncertainty
keep datem bloom_uncertainty

tempfile Bloom
save "`Bloom'", replace
* ******************
* Martin data on svix: https://personal.lse.ac.uk/martiniw/
import excel $RawTS/SVIX2.xls, sheet("Sheet1") clear

gen date=date(A, "MDY")
format date %td

rename B svix1
rename C svix3
rename D svix6
rename E svix9
rename F svix12

gen datem=mofd(date)
format datem %tm

sort datem date

* pick end of the month
collapse (lastnm) svix12, by(datem)

tempfile IanMartin
save "`IanMartin'", replace
* **************************************************************************************
* Data from Lettau's website:  https://sites.google.com/view/martinlettau/data
import delimited $RawTS/cay_current2019Q3.csv, clear

* date is a string
split date,g(part) p("-")
foreach var of varlist part* {
	destring `var', replace
}
* now put it back together
generate datem=mofd(mdy(part2,part3,part1))
format datem %tm
rename cay* cay
keep datem cay
tempfile cay
save "`cay'", replace
* **************************************************************************************
*data on spc -- API key for FRED: 011d1d6ae23ecb1ad6797acae16048d9
set fredkey 011d1d6ae23ecb1ad6797acae16048d9, permanently

import fred PCE PCEPI POP, clear
generate ctotal = PCE /(POP*PCEPI)
drop PCE POP PCEPI

generate c = log(ctotal)
keep if c<.

generate t=_n
tsset t

scalar phi = 0.90
generate x = c[1] 		if t==1
replace x =  phi*L.x+ (1-phi)*c	if t>1

generate spc = 1 - exp(x)/exp(c)

* note: FED uses first-of-the-month date convention but WRDS uses last-trading-day-of-the-month
gen datem=mofd(daten)-1
format datem %tm
keep datem spc
tempfile spc
save "`spc'", replace
* **************************************************************************************
import excel $RawTS/shiller_updated.xls, sheet("formatted") firstrow clear
keep if Date<.
drop Fraction

foreach var of varlist RealDiv* RealEar* CAPE CAPD RCAE RCAD D E {
	destring `var', replace
}

* generate date; compute date in monthly format using mofd function
gen yr=int(Date)
gen mo=100*(Date-yr)

generate dateshiller=mdy(mo+1,1,yr  )-1 if mo<=11
replace  dateshiller=mdy(1   ,1,yr+1)-1	if mo==12
format dateshiller %td

gen datem=mofd(dateshiller)
format datem %tm

tsset datem

tssmooth ma CAE2= E, window(120 0 0)
gen e_cae2=ln(E)-ln(L60.CAE2)
gen e_cae3=L3.e_cae2

drop e_cae2 
tempfile shiller
save "`shiller'", replace
* **************************************************************************************
import sas caldt totval spindx vwretd vwretx sprtrn using $WRDS/CRSP/msp500.sas7bdat, clear
keep if spindx<.

rename caldt date
format date %td

gen datem=mofd(date)
format datem %tm

tsset datem
gen lnret=ln(1+sprtrn)

* Compute compounded returns
gen RetYr1=1
gen RetYr3=1
gen RetYr5=1
forvalues x=1(1)12 {
	replace RetYr1=RetYr1*(1+F`x'.sprtrn)
}
forvalues x=1(1)36 {
	replace RetYr3=RetYr3*(1+F`x'.sprtrn)
}
forvalues x=1(1)60 {
	replace RetYr5=RetYr5*(1+F`x'.sprtrn)
}
* Compute cumulative log returns  
gen lnret1=ln(RetYr1)
gen lnret2=F12.lnret1+lnret1
gen lnret3=F24.lnret1+lnret2
gen lnret4=F36.lnret1+lnret3
gen lnret5=F48.lnret1+lnret4
gen lnret6=F60.lnret1+lnret5
gen lnret7=F72.lnret1+lnret6

* compound lagged returns (data is monthly)
gen lagret1=0
forvalues x=0(1)11 {
	replace lagret1=lagret1+L`x'.lnret
}
gen lagret5=0
forvalues x=0(1)59 {
	replace lagret5=lagret5+L`x'.lnret
}
keep date datem lnret* lagret1 lagret5 RetYr*
tempfile msp
save "`msp'", replace
* *******************************************
* ADD RISK FREE
import sas using $WRDS/CRSP/mcti.sas7bdat, clear
gen datem=mofd(caldt)
format datem %tm

gen RFYr1=1
gen RFYr3=1
gen RFYr5=1

tsset datem
forvalues x=1(1)12 {
	replace RFYr1=RFYr1*(1+F`x'.t30ret)
}
forvalues x=1(1)36 {
	replace RFYr3=RFYr3*(1+F`x'.t30ret)
}
forvalues x=1(1)60 {
	replace RFYr5=RFYr5*(1+F`x'.t30ret)
}
keep datem RFYr*
* *******************************************
merge 1:1 datem using "`msp'"
keep if _merge==3
drop _merge

merge 1:1 datem using "`shiller'"
keep if _merge==3
drop _merge
* **************************************************************************************
merge 1:1 datem using $WorkTS/figure01, keepusing(datem statpers p10_E)
drop _merge
* **************************************************************************************
merge 1:1 datem using $WorkTS/prgm02, keepusing(datem LTG_Nagel ActualE* lnret5 LTG fcastE* )
drop _merge
replace LTG=LTG/100
tempfile tmp
save "`tmp'", replace
* **************************************************************************************
* Add Nagel data on experienced dividends (downloaded from Stefan Nagel's website)
import excel $RawTS/FadingMemoryData/APFM_2022Mar.xlsx, sheet("Quarterly") firstrow clear
rename yyyy year
generate month=03 if qtr==1
replace  month=06 if qtr==2
replace  month=09 if qtr==3
replace  month=12 if qtr==4
gen datem=mofd(mdy(month,1,year))
format datem %tm

keep if mdy(month,1,year)>mdy(11,1,1981)

keep datem expd 
tempfile nagel
save "`nagel'", replace
* *******************************
merge 1:1 datem using "`tmp'"
drop if _merge==1
drop _merge
* *******************************
gen ep=ln(E/P)
gen dp=ln(D/P)	if LTG<.
quietly: su  dp 
scalar rho=1/(1+exp(r(mean)))

gen r1=lnret1
gen r2=r1+rho^1*(lnret2-lnret1)
gen r3=r2+rho^2*(lnret3-lnret2)
gen r4=r3+rho^3*(lnret4-lnret3)
gen r5=r4+rho^4*(lnret5-lnret4)
gen r6=r5+rho^5*(lnret6-lnret5)
gen r7=r6+rho^6*(lnret7-lnret6)
********
tsset datem
gen xret1=lnret1 - ln(RFYr1)
gen xret2=xret1  + rho^1*(F12.lnret1 -ln(F12.RFYr1))
gen xret3=xret2  + rho^2*(F24.lnret1 -ln(F24.RFYr1))
gen xret4=xret3  + rho^3*(F36.lnret1 -ln(F36.RFYr1))
gen xret5=xret4  + rho^4*(F48.lnret1 -ln(F48.RFYr1))
* ******
gen STGE1=ln(fcastE1/E)
gen STGE2=ln(fcastE2/fcastE1)

gen d1LTG=LTG-L12.LTG
gen d3LTG=LTG-L36.LTG
gen d5LTG=LTG-L60.LTG
gen L5LTG=L60.LTG
gen L1LTG=L12.LTG

gen   STG=ln(fcastE2/fcastE1)
gen dSTG1=ln(fcastE1/ActualE0)-ln(L12.fcastE2/L12.fcastE1)
gen dSTG2=ln(fcastE2/fcastE1 )-ln(L12.fcastE3/L12.fcastE2)

* define errors
gen errorSTG1=ln(ActualE1/ActualE0)-ln(fcastE1/ActualE0)
gen errorSTG2=ln(ActualE2/ActualE1)-ln(fcastE2/fcastE1)

* Long-term stuff
gen  errorLTGA=ln(ActualE5/L3.E)/5-LTG
gen  errorLTGB=ln(L3.ActualE5/L3.ActualE0)/5-LTG
gen  errorDeLO=ln(ActualE5/ActualE3)/2-LTG

gen  ActualE7=F24.ActualE5
gen  errorLTG3=ln(L3.ActualE3/L3.ActualE0)/3-LTG
gen  errorLTG7=ln(L3.ActualE7/L3.ActualE0)/7-LTG
drop ActualE7

gen  eNIC1=ln(L3.ActualE1/L51.ActualE0)/5-L48.LTG
gen  eNIC2=ln(L3.ActualE2/L39.ActualE0)/5-L36.LTG
gen  eNIC3=ln(L3.ActualE3/L27.ActualE0)/5-L24.LTG
gen  eNIC4=ln(L3.ActualE4/L15.ActualE0)/5-L12.LTG
gen  eNIC5=ln(L3.ActualE5/ L3.ActualE0)/5-LTG

tempfile TimeSeries01
save "`TimeSeries01'", replace
* **************************************************************************************
* Get Graham data on expected returns
* https://www.richmondfed.org/research/national_economy/cfo_survey/data_and_results
* *******************************************
import excel using $RawTS/DukeSurvey.xlsx, sheet("summary_formatted") firstrow clear
keep date1 ret1yr_mean ret10yr_mean

destring ret1yr_mean , gen(duke01Yr)
destring ret10yr_mean, gen(duke10Yr)
/* ******************** */
gen datem=mofd(date1)
format datem %tm
keep if date1<mdy(7,1,2001)
keep datem duke*

* change the date of the 1st observation -- date is October, make it September.  Else need to intepolate to compute one-yr changes
replace datem=datem-1 if _n==1

tempfile DukeNew
save "`DukeNew'", replace
/* ******************** */
import excel $RawTS/current_historical_cfo_data.xls, sheet("through_Q1_2020") firstrow clear
keep quarter year sp_1_exp sp_10_exp 
rename sp_1_exp   duke01Yr
rename sp_10_exp  duke10Yr 
* fix typo
replace duke10Yr=6.30 if year==2013 & quarter==4
tempfile part1
save "`part1'", replace

import excel $RawTS/current_historical_cfo_data.xls, sheet("from_Q2_2020on") firstrow clear
keep quarter year sp_12moexp_2 sp_10yrexp_2

rename sp_12moexp_2 duke01Yr
rename sp_10yrexp_2 duke10Yr 

append using "`part1'"
sort year quarter

gen mo=quarter*3

gen date=mdy(mo,15,year)
format date %td

gen datem=mofd(date)
format datem %tm
keep datem duke*

keep if datem<.
append using "`DukeNew'"

tsset datem
generate d_duke01=duke01Yr-L12.duke01Yr 
generate d_duke10=duke10Yr-L12.duke10Yr 
generate d5_duke01=duke01Yr-L60.duke01Yr 

su d_duke01
keep if duke01Yr<.

tempfile Duke
save "`Duke'", replace
* *********************
use "/mnt/brownresearch/ECON_LaPorta/rlaporta/research/Aggregate/Adi/data_for_andrei_and_lapo.dta", clear
d , varlist
* data is quarterly; assume that it reflects eoq 
gen datem = mofd(dofq(yq))+2
format datem %tm
rename cay cay_adi

tempfile adi
save "`adi'", replace
* *********************
use "`TimeSeries01'", clear
* ********************************************************************** */
merge 1:1 datem using "`Bloom'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`IanMartin'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`Duke'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`cay'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`spc'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`yield'"
drop if _merge==2
drop _merge

merge 1:1 datem using "`adi'"
drop if _merge==2
drop _merge cay_adi

save "`TimeSeries01'", replace
* *******************************************
* get data on expected inflation (https://www.philadelphiafed.org/surveys-and-data/real-time-data-research/inflation-forecasts)
import excel $RawTS/SurveyProfessionalForecasters/Inflation.xlsx, sheet("INFLATION") firstrow case(lower) clear
ds, has(type string) 
local strvars "`r(varlist)'" 

foreach var in `strvars' {
	replace `var'="." if index(`var',"N/A")
	destring `var', replace
}	
tempfile spf
save "`spf'", replace
* ********************
import excel $RawTS/SurveyProfessionalForecasters/Additional-CPIE10.xlsx, sheet("Sheet1") firstrow case(lower) clear
keep if !missing(surveydate)

gen year=substr(surveydate,1,4)
gen quarter= substr(surveydate,6,6)
destring year quarter, replace

merge 1:1 year quarter using "`spf'"
drop _merge

sort year quarter
pwcorr infcpi10yr livingston bluechip combined

* use Blue Chip data for 10-year inflation forecasts when Livingston is unavailable
generate exp10Yrinf=infcpi10yr
replace  exp10Yrinf=combined if infcpi10yr==.
drop combined bluechip infcpi10yr

* use forecasts for GDP deflator when forecasts for cpi inflation are missing
generate exp1Yrinf=infcpi1yr
replace  exp1Yrinf=infpgdp1yr if infcpi1yr==.
drop infcpi1yr infpgdp1yr 
gen date=mdy(quarter*3,1,year)
format date %td

gen datem=mofd(date)
format datem %tm
drop date
* ********************
tsset datem
tsfill  

ipolate  exp1Yrinf datem, gen(ExpInf)
ipolate exp10Yrinf datem, gen(ExpInf10)

keep datem ExpInf*
* ******************************
merge 1:1 datem using "`TimeSeries01'"
drop if _merge==1
drop _merge

gen LTGR=LTG-ExpInf/100
gen STGE2R=STGE2-ExpInf/100
* *******************************************
* *******************************************
save $LastXS/TimeSeries01, replace
* *******************************************
* *******************************************
